package za.org.jcicada.datastore.impl.jdbc;

import java.net.URI;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import za.org.jcicada.datastore.IDataStoreClient;
import za.org.jcicada.datastore.impl.DataStoreServerAbstract;
import za.org.jcicada.genss.messages.Result;
import za.org.jcicada.genss.messages.SuccessCode;
import za.org.jcicada.genss.sensor.ISensorValue;
import za.org.jcicada.genss.sensor.SensorStatus;
import za.org.jcicada.genss.sensor.SensorURN;
import za.org.jcicada.genss.sensor.impl.SensorStatusValue;
import za.org.jcicada.genss.sensor.impl.SensorValue;

public class JDBCDataStoreServer extends DataStoreServerAbstract {

	private static final Log logger = LogFactory.getLog(JDBCDataStoreServer.class);
	JDBC jdbc;
	Map<SensorURN, Long> sensorIds = new ConcurrentHashMap<SensorURN,Long>();
	Map<SensorURN, Class<?>> sensorTypes = new ConcurrentHashMap<SensorURN,Class<?>>();
	static final String DEFAULT_TYPE_TABLE = "sensor_status_strings";
	static Map<Class<?>, String> typeTables = new ConcurrentHashMap<Class<?>, String>();
	static {
		typeTables.put(Boolean.class, "sensor_status_booleans");
		typeTables.put(Integer.class, "sensor_status_integers");
		typeTables.put(Float.class, "sensor_status_floats");
	}
	
	@Override
	public void initialize(URI connectionURI) throws Exception {
		jdbc = JDBC.getInstance(connectionURI);
	}
	
	@Override
	public void storeSensor(SensorURN sensorURN, ISensorValue value) {
		Connection connection = null;
		try {
			connection = jdbc.getConnection();
			Object object = value.getValue();
			long sensorId = setSensorType(connection, sensorURN, object);
			String typeTable = getTypeTable(object == null ? null : object.getClass());
			PreparedStatement insert = connection.prepareStatement("INSERT INTO " + typeTable + " (sensor_id,sampled_at,value) VALUES (?,?,?)");
			insert.setLong(1, sensorId);
			insert.setLong(2, value.getTime());
			insert.setObject(3, object);
			insert.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	@Override
	public void storeSensorStatus(SensorURN sensorURN, SensorStatusValue statusValue) {
		Connection connection = null;
		try {
			connection = jdbc.getConnection();
			PreparedStatement insertSensorStatus = connection.prepareStatement("INSERT INTO sensor_statuses (sensor_id,sampled_at,status,message) VALUES (?,?,?,?)");
			insertSensorStatus.setLong(1, getSensorId(connection, sensorURN));
			insertSensorStatus.setLong(2, statusValue.getTime());
			insertSensorStatus.setString(3, statusValue.getStatus().name());
			insertSensorStatus.setString(4, statusValue.getMessage());
			insertSensorStatus.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	@Override
	public void retrieveSensorHistory(IDataStoreClient client, SensorURN sensorURN, Date from, Date to) {
		Connection connection = null;
		List<ISensorValue> values = new LinkedList<ISensorValue>();
		Result result = new Result();
		try {
			connection = jdbc.getConnection();
			long sensorId = getSensorId(connection, sensorURN);
			Class<?> type = getSensorType(connection, sensorURN, sensorId);
			String typeTable = getTypeTable(type);
			PreparedStatement selectPrevious = connection.prepareStatement("SELECT sampled_at,value FROM " + typeTable + " WHERE sensor_id = ? AND sampled_at < ? ORDER BY sampled_at DESC");
			selectPrevious.setMaxRows(1);
			appendValues(sensorId, from, to, values, selectPrevious);
			PreparedStatement select = connection.prepareStatement("SELECT sampled_at, value FROM " + typeTable + " WHERE sensor_id = ? AND sampled_at >= ? AND sampled_at <= ? ORDER BY sampled_at");
			select.setLong(3, to.getTime());
			appendValues(sensorId, from, to, values, select);
		} catch (Exception e) {
			logger.warn("Failed to retrieveSensorHistory", e);
			result = new Result(SuccessCode.COMPLETE_FAILURE, e.getMessage());
		}
		client.replySensorHistory(result, sensorURN, from, to, values);
	}

	@Override
	public void retrieveStatusHistory(IDataStoreClient client, SensorURN sensorURN, Date from, Date to) {
		Connection connection = null;
		List<SensorStatusValue> values = new LinkedList<SensorStatusValue>();
		Result result = new Result();
		try {
			connection = jdbc.getConnection();
			long sensorId = getSensorId(connection, sensorURN);
			PreparedStatement selectSensorStatusHistoryPrevious = connection.prepareStatement("SELECT sampled_at,status,message FROM sensor_statuses WHERE sensor_id = ? AND sampled_at < ? ORDER BY sampled_at DESC");
			selectSensorStatusHistoryPrevious.setMaxRows(1);
			appendStatuses(sensorId, from, values, selectSensorStatusHistoryPrevious);
			PreparedStatement selectSensorStatusHistory = connection.prepareStatement("SELECT sampled_at,status,message FROM sensor_statuses WHERE sensor_id = ? AND sampled_at >= ? AND sampled_at <= ? ORDER BY sampled_at");
			selectSensorStatusHistory.setLong(3, to.getTime());
			appendStatuses(sensorId, from, values, selectSensorStatusHistory);
		} catch (SQLException e) {
			logger.warn("Failed to retrieveStatusHistory", e);
			result = new Result(SuccessCode.COMPLETE_FAILURE, e.getMessage());
		}
		client.replyStatusHistory(result, sensorURN, from, to, values);
	}
	
	private void appendValues(long sensorId, Date from, Date to, List<ISensorValue> values, PreparedStatement stmt) throws SQLException {
		stmt.setLong(1, sensorId);
		stmt.setLong(2, from.getTime());
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			long sampled_at = rs.getLong(1);
			Object value = rs.getObject(2);
			values.add(new SensorValue(sampled_at, value));
		}
	}

	private void appendStatuses(long sensorId, Date from,
			List<SensorStatusValue> values,	PreparedStatement stmt) throws SQLException {
		stmt.setLong(1, sensorId);
		stmt.setLong(2, from.getTime());
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			long sampled_at = rs.getLong(1);
			SensorStatus status = SensorStatus.valueOf(rs.getString(2));
			String message = rs.getString(3);
			values.add(new SensorStatusValue(status, sampled_at, message));
		}
	}
	
	private long getSensorId(Connection connection, SensorURN sensorURN) throws SQLException {
		Long sensorId = sensorIds.get(sensorURN);
		if (sensorId == null) {
			PreparedStatement selectSensor = connection.prepareStatement("SELECT id FROM sensors WHERE sensor_urn = ?");
			selectSensor.setString(1, sensorURN.toString());
			ResultSet rs = selectSensor.executeQuery();
			if (rs.next()) {
				sensorId = rs.getLong(1);
			} else {
				PreparedStatement insertSensor = connection.prepareStatement("INSERT INTO sensors (sensor_urn) VALUES (?)", Statement.RETURN_GENERATED_KEYS);
				insertSensor.setString(1, sensorURN.toString());
				insertSensor.executeUpdate();
				rs = insertSensor.getGeneratedKeys();
				rs.next();
				sensorId = rs.getLong(1);
				sensorIds.put(sensorURN, sensorId);
			}
		}
		return sensorId;
	}
	
	private long setSensorType(Connection connection, SensorURN sensorURN, Object object) throws SQLException {
		long sensorId = getSensorId(connection, sensorURN);
		if (object != null) {
			Class<?> type = object.getClass();
			if (!type.equals(sensorTypes.get(sensorURN))) {
				sensorTypes.put(sensorURN, type);
				PreparedStatement updateSensorType = connection.prepareStatement("UPDATE sensors SET type = ? WHERE sensor_urn = ?");
				updateSensorType.setString(1, type.getName());
				updateSensorType.setString(2, sensorURN.toString());
				updateSensorType.executeUpdate();
			}
		}
		return sensorId;
	}
	
	private Class<?> getSensorType(Connection connection, SensorURN sensorURN, long sensorId) throws SQLException, ClassNotFoundException {
		Class<?> type = sensorTypes.get(sensorURN);
		if (type == null) {
			PreparedStatement selectSensorType = connection.prepareStatement("SELECT type FROM sensors WHERE id = ?");
			selectSensorType.setLong(1, sensorId);
			ResultSet rs = selectSensorType.executeQuery();
			rs.next();
			String typeName = rs.getString(1);
			type = typeName == null ? String.class : Class.forName(rs.getString(1));
			sensorTypes.put(sensorURN, type);
		}
		return type;
	}
	
	private String getTypeTable(Class<?> type) {
		String table = DEFAULT_TYPE_TABLE;
		if (type != null && typeTables.containsKey(type)) {
			table = typeTables.get(type);
		}
		return table;
	}

}
